package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.log4j.Logger;

import util.Constants;

import db.DbDictionaries;
import core.Dictionary;

public class DbWords {
    public static Logger LOG = Logger.getLogger("db.DbWords");

    // add new word to m_words table
    public static boolean addWord(String word, String meaning, int dictID) {
        boolean bOK = false;
        int indict = 0;
        // word = word.replace("'", "`");
        // meaning = meaning.replace("'","`");
        Dictionary dict = DbDictionaries.getDictionary(dictID);
        if (dict.existWord(word)) {
            indict = 1;
        }
        Connection cnn = null;
        CallableStatement cstat = null;
        ResultSet rs = null;
        try {
            cnn = DbController.getConnection();
            cstat = cnn
                    .prepareCall("select*from m_words where m_dictID=" + dictID + " and m_word=N'" + word + "'");
            rs = cstat.executeQuery();
            if (!rs.next()) { // if word does not exist
                rs.close();
                cstat.close();
                cstat = cnn.prepareCall("{call m_words_insert(?, ?, ?)}");
                cstat.setInt(1, dictID);
                cstat.setString(2, "N'" + word +"'");
                cstat.setInt(3, indict);                
                bOK = (cstat.executeUpdate() == 1);
            } else {
                bOK = true;
            }
            if (bOK) {
                bOK = false;
                indict = -1;
                cstat.close();
                // get wordID
                cstat = cnn.prepareCall("select m_id from m_words where m_dictID=" + dictID + " and m_word=N'"
                        + word + "'");
                rs = cstat.executeQuery();
                if (rs.next()) {
                    indict = rs.getInt("m_id");
                    bOK = true;
                }
            }
            if (bOK) {
                rs.close();
                cstat.close();
                // insert into m_meanings table
                cstat = cnn.prepareCall("{call m_meanings_insert(?, ?)}");
				cstat.setInt(1, indict);
				cstat.setString(2, meaning);
                bOK = (cstat.executeUpdate() == 1);
            }
        } catch (Exception ex) {
            LOG.error("addWord", ex);
        } finally {
            try {
                if (cstat != null) {
                    cstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    public static boolean addWord(String word, String meaning, int dictID, String sessionID) {
        boolean bOK = false;
        int indict = 0;
        Dictionary dict = DbDictionaries.getDictionary(dictID);
        if (dict.existWord(word)) {
            indict = 1;
        }
        meaning = meaning.replace("'", "`");
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            cnn = DbController.getConnection();
            pstat = cnn
                    .prepareStatement("select*from m_words where m_dictID=" + dictID + " and m_word=N'" + word + "'");
            rs = pstat.executeQuery();
            if (!rs.next()) { // if word does not exist
                rs.close();
                pstat.close();
                pstat = cnn.prepareStatement("insert into m_words(m_dictID,m_word,m_indict) values(" + dictID + ",N'"
                        + word + "'," + indict + ")");
                bOK = (pstat.executeUpdate() == 1);
            } else {
                bOK = true;
            }
            if (bOK) {
                bOK = false;
                indict = -1;
                pstat.close();
                // get wordID
                pstat = cnn.prepareStatement("select m_id from m_words where m_dictID=" + dictID + " and m_word=N'"
                        + word + "'");
                rs = pstat.executeQuery();
                if (rs.next()) {
                    indict = rs.getInt("m_id");
                    bOK = true;
                }
            }
            if (bOK) {
                rs.close();
                pstat.close();
                // insert into m_meanings table
                pstat = cnn.prepareStatement("update m_meanings set m_meaning='" + meaning + "' where m_wordID="
                        + indict + " and m_sessionID='" + sessionID + "'");
                if (pstat.executeUpdate() >= 1) {
                    bOK = true;
                } else {
                    bOK = (pstat.executeUpdate("insert into m_meanings(m_wordID,m_meaning,m_sessionID) values("
                            + indict + ",N'" + meaning + "','" + sessionID + "')") == 1);
                }
            }
        } catch (Exception ex) {
            LOG.error("addWord with sessionID", ex);
            LOG.debug("indict=" + indict);
            LOG.debug("meaning=" + meaning);
            LOG.debug("sessionID=" + sessionID);
        } finally {
            try {
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    public static boolean deleteWord(int wordID) {
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstatDelApproving = null;
        PreparedStatement pstatDelMeaning = null;
        PreparedStatement pstatDelWord = null;
        try {
            cnn = DbController.getConnection();
            pstatDelApproving = cnn.prepareStatement("delete from m_approvings where m_wordID=" + wordID);
            pstatDelMeaning = cnn.prepareStatement("delete from m_meanings where m_wordID=" + wordID);
            pstatDelWord = cnn.prepareStatement("delete from m_words where m_id=" + wordID);
            pstatDelApproving.executeUpdate();
            pstatDelMeaning.executeUpdate();
            pstatDelWord.executeUpdate();
        } catch (Exception ex) {
            LOG.error("deleteWord", ex);
        } finally {
            try {
                if (pstatDelWord != null) {
                    pstatDelWord.close();
                }
                if (pstatDelMeaning != null) {
                    pstatDelMeaning.close();
                }
                if (pstatDelApproving != null) {
                    pstatDelApproving.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    public static boolean deleteWord(int id, String word, int code, int dictID, String sessionID) {
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        boolean bOK = false;
        String word2 = Constants.BLANK_STRING;
        try {
            cnn = DbController.getConnection();
            switch (code) {
            case 1: // delete reviewing word
                pstat = cnn.prepareStatement("delete from m_meanings where m_wordID=" + id + " and m_sessionID='"
                        + sessionID + "'");
                pstat.executeUpdate();
                rs = pstat.executeQuery("select * from m_meanings where m_wordID=" + id);
                if (!rs.next()) {
                    pstat.executeUpdate("delete from m_approvings where m_wordID = " + id);
                    pstat.executeUpdate("delete from m_words where m_id = " + id);
                }
                bOK = true;
                break;
            case 2: // delete approved word
                pstat = cnn.prepareStatement("select m_word from m_approveds where m_id=" + id);
                rs = pstat.executeQuery();
                if (rs.next()) {
                    word2 = rs.getString("m_word");
                }
                pstat.executeUpdate("delete from m_approveds where m_id = " + id);
                if (!Constants.BLANK_STRING.equals(word2)) {
                    core.Dictionary dict = DbDictionaries.getDictionary(dictID);
                    if (dict != null) {
                        dict.remove1Word(word);
                        DbDictionaries.updateDelCount(dictID, dict.delCount);
                    }
                }
                bOK = true;
                break;
            default:
                break;
            }
        } catch (Exception ex) {
            LOG.error("deleteWord with sessionID", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    public static boolean changeWord(int id, String word, String meaning, int code, int dictID, String sessionID) {
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        boolean sameWord = false;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select m_word from m_words where m_id=" + id);
            rs = pstat.executeQuery();
            if (rs.next()) {
                if (word.equals(rs.getString("m_word"))) {
                    sameWord = true;
                }
            }
            switch (code) {
            case 1: // change reviewing word
                if (sameWord) {
                    pstat.executeUpdate("update m_meanings set m_meaning='" + meaning + "' where m_wordID = " + id
                            + " and m_sessionID='" + sessionID + "'");
                } else {
                    addWord(word, meaning, dictID, sessionID);
                }
                bOK = true;
                break;
            case 2: // change approved word
                if (sameWord) {
                    pstat.executeUpdate("update m_approveds set m_meaning='" + meaning + "' where m_id=" + id);
                } else {
                    DbApproveds.addApproved(dictID, word, meaning, sessionID);
                }

                bOK = true;
                break;
            default:
                break;
            }
        } catch (Exception ex) {
            try {
                java.io.FileWriter fw = new java.io.FileWriter("h:/abc.txt");
                fw.write(ex.toString());
                fw.close();
            } catch (Exception exx) {
            }
            LOG.error("changeWord", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

    public static String[][] getApprovingWordsBySession(int dictID, String sessionID) {
        String[][] list = null;
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        int count = 0;
        try {
            cnn = DbController.getConnection();
            pstat = cnn
                    .prepareStatement("select count(*) as num from m_meanings meaning,m_words word where word.m_id = meaning.m_wordID and word.m_dictID = "
                            + dictID + " and m_sessionID='" + sessionID + "'");
            rs = pstat.executeQuery();
            if (rs.next()) {
                count = rs.getInt("num");
            }
            if (count > 0) {
                list = new String[count][3];
                rs = pstat
                        .executeQuery("select word.m_id as m_id,word.m_word as m_word, meaning.m_meaning as m_meaning  from m_words word, m_meanings meaning where word.m_dictID = "
                                + dictID
                                + " and word.m_id = meaning.m_wordID and meaning.m_sessionID = '"
                                + sessionID
                                + "'");
                int idx = 0;
                for (idx = 0; idx < count; idx++) {
                    if (rs.next()) {
                        list[idx][0] = String.valueOf(rs.getInt("m_id"));
                        list[idx][1] = rs.getString("m_word");
                        list[idx][2] = rs.getString("m_meaning");
                    } else {
                        break;
                    }
                }
            }
        } catch (Exception ex) {
            LOG.error("getApprovingWordsBySession", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return list;
    }

    public static String[][] getApprovedWordsBySession(int dictID, String sessionID) {
        String[][] list = null;
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        int count = 0;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select count(*) as num from m_approveds where m_dictID = " + dictID
                    + " and m_sessionID = '" + sessionID + "'");
            rs = pstat.executeQuery();
            if (rs.next()) {
                count = rs.getInt("num");
            }
            if (count > 0) {
                list = new String[count][3];
                rs = pstat.executeQuery("select m_id,m_word,m_meaning from m_approveds where m_dictID = " + dictID
                        + " and m_sessionID = '" + sessionID + "'");
                int idx = 0;
                for (idx = 0; idx < count; idx++) {
                    if (rs.next()) {
                        list[idx][0] = String.valueOf(rs.getInt("m_id"));
                        list[idx][1] = rs.getString("m_word");
                        list[idx][2] = rs.getString("m_meaning");
                    } else {
                        break;
                    }
                }
            }
        } catch (Exception ex) {
            LOG.error("getApprovedWordsBySessionID", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return list;
    }

    public static boolean approveWord(String word, String meaning, String sessionID, int dictID) {
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        int wordID = -1;
        boolean bOK = false;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select m_id  from m_words where m_word = '" + word + "' and m_dictID = "
                    + dictID);
            rs = pstat.executeQuery();
            if (rs.next()) {
                wordID = rs.getInt("m_id");
                bOK = DbApproveds.addApproved(dictID, wordID, word, meaning, sessionID);
            } else {
                bOK = DbApproveds.addApproved(dictID, word, meaning, sessionID);
            }
        } catch (Exception ex) {
            LOG.error("approvvedWord", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }
}
